package cn.ssdcc.dkserver.mapper;

import cn.ssdcc.dkserver.entity.Music;
import cn.ssdcc.dkserver.entity.MusicGroup;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface MusicMapper {

    //创建音乐表
    @Insert("CREATE TABLE IF NOT EXISTS `music` (\n" +
            "\t`id` integer NOT NULL AUTO_INCREMENT COMMENT '主键',\n" +
            "\t`name` varchar(100) NOT NULL COMMENT '歌名',\n" +
            "\t`arname` varchar(1000) NOT NULL COMMENT '歌手名',\n" +
            "\t`alname` varchar(1000) NOT NULL COMMENT '专辑名',\n" +
            "\t`file` varchar(100) NOT NULL COMMENT '文件路径',\n" +
            "\t`img` varchar(100) NOT NULL COMMENT '封面路径',\n" +
            "\t`type` varchar(100) NOT NULL COMMENT '音乐类型',\n" +
            "\t`userid` varchar(100) NOT NULL COMMENT '上传者',\n" +
            "\t`thirdid` varchar(100) COMMENT '第三方id',\n" +
            "\t`uuid` varchar(100) NOT NULL COMMENT 'uuid',\n" +
            "\tPRIMARY KEY (`id`)\n" +
            ")")
    int createMusicTable();

    //创建歌单表
    @Insert("CREATE TABLE IF NOT EXISTS `musicgroup` (\n" +
            "\t`id` integer NOT NULL AUTO_INCREMENT COMMENT '主键',\n" +
            "\t`name` varchar(100) NOT NULL COMMENT '歌单名',\n" +
            "\t`about` varchar(1000) NOT NULL COMMENT '描述',\n" +
            "\t`img` varchar(100) NOT NULL COMMENT '封面路径',\n" +
            "\t`userid` varchar(100) NOT NULL COMMENT '上传者',\n" +
            "\t`uuid` varchar(100) NOT NULL COMMENT 'uuid',\n" +
            "\tPRIMARY KEY (`id`)\n" +
            ")")
    int createMusicGroupTable();

    //创建歌单表
    @Insert("CREATE TABLE IF NOT EXISTS `musicandgroup` (\n" +
            "\t`id` integer NOT NULL AUTO_INCREMENT COMMENT '主键',\n" +
            "\t`groupid` varchar(100) NOT NULL COMMENT '歌单id',\n" +
            "\t`musicid` varchar(100) NOT NULL COMMENT '音乐id',\n" +
            "\tPRIMARY KEY (`id`)\n" +
            ")")
    int createMusicAndGroupTable();

    @Insert("INSERT INTO music\n" +
            "(name, arname, alname, file, img, `type`, userid, thirdid,uuid)\n" +
            "VALUES(#{music.name}, #{music.arname}, #{music.alname}, #{music.file}, #{music.img}, #{music.type}, #{music.userid}, #{music.thirdid}, #{music.uuid})")
    int insertMusic(@Param("music") Music music);

    @Select("SELECT id, name, arname, alname, file\n" +
            "\t, img, `type`, userid, thirdid\n" +
            "FROM music\n" +
            "WHERE id = #{id}\n" +
            "\tOR name LIKE #{like}\n" +
            "\tOR arname LIKE #{like}\n" +
            "\tOR alname LIKE #{like}\n" +
            "\tOR thirdid = #{id}")
    List<Music> selectMusic(@Param("like")String like,@Param("id")String id);

    @Select("SELECT id, name, arname, alname, file\n" +
            "\t, img, `type`, userid, thirdid\n" +
            "FROM music\n" +
            "WHERE id = #{id}")
    List<Music> selectMusicById(@Param("id")String id);

    @Select("SELECT id, name, arname, alname, file\n" +
            "\t, img, `type`, userid, thirdid\n" +
            "FROM music\n" +
            "WHERE thirdid = #{id}")
    List<Music> selectMusicByThirdid(@Param("id")String id);

    @Select("SELECT id, name, about, img, userid\n" +
            "FROM musicgroup")
    List<MusicGroup> selectMusicGroup();

    @Select("SELECT id, name, about, img, userid\n" +
            "FROM musicgroup WHERE uuid = #{uuid}")
    List<MusicGroup> selectMusicGroupByUuid(@Param("uuid") String uuid);

    @Select("SELECT id, name, about, img, userid\n" +
            "FROM musicgroup WHERE id = #{id}")
    List<MusicGroup> selectMusicGroupById(@Param("id") String id);

    @Select("SELECT id, name, about, img, userid\n" +
            "FROM musicgroup")
    List<MusicGroup> selectMusicGroupList();

    @Insert("INSERT INTO musicgroup\n" +
            "(name, about, img, userid,uuid)\n" +
            "VALUES(#{musicgroup.name}, #{musicgroup.about}, #{musicgroup.img}, #{musicgroup.userid}, #{musicgroup.uuid})\n")
    int inserteMusicGroup(@Param("musicgroup") MusicGroup musicgroup);

    @Delete("DELETE FROM musicandgroup\n" +
            "WHERE groupid = #{groupid}")
    int deleteMusicAndGroupByGroupId(@Param("groupid")String groupid);

    @Insert("INSERT INTO musicandgroup\n" +
            "(groupid, musicid)\n" +
            "VALUES(#{groupid}, #{musicid});")
    int insertMusicAndGroup(@Param("groupid") String groupid,@Param("musicid") String musicid);

    @Select("SELECT musicid\n" +
            "FROM musicandgroup WHERE groupid = #{groupid}")
    List<String> selectMusicAndGroupGroupid(@Param("groupid") String groupid);

    @Delete("DELETE FROM musicgroup\n" +
            "WHERE id = #{groupid}\n")
    int deleteMusicGroupByGroupId(@Param("groupid")String groupid);

    @Update("UPDATE musicgroup\n" +
            "SET name=#{musicgroup.name}, about=#{musicgroup.about}, img=#{musicgroup.img}\n" +
            "WHERE id=#{musicgroup.id};")
    int updateMusicGroup(@Param("musicgroup") MusicGroup musicgroup);

}
